/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.commons.configuration;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import javax.sql.DataSource;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.logging.LogFactory;
/**
* Configuration stored in a database. The properties are retrieved from a
* table containing at least one column for the keys, and one column for the
* values. It's possible to store several configurations in the same table by
* adding a column containing the name of the configuration. The name of the
* table and the columns is specified in the constructor.
*
* <h4>Example 1 - One configuration per table</h4>
*
* <pre>
* CREATE TABLE myconfig (
* `key` VARCHAR NOT NULL PRIMARY KEY,
* `value` VARCHAR
* );
*
* INSERT INTO myconfig (key, value) VALUES ('foo', 'bar');
*
*
* Configuration config = new DatabaseConfiguration(datasource, "myconfig", "key", "value");
* String value = config.getString("foo");
* </pre>
*
* <h4>Example 2 - Multiple configurations per table</h4>
*
* <pre>
* CREATE TABLE myconfigs (
* `name` VARCHAR NOT NULL,
* `key` VARCHAR NOT NULL,
* `value` VARCHAR,
* CONSTRAINT sys_pk_myconfigs PRIMARY KEY (`name`, `key`)
* );
*
* INSERT INTO myconfigs (name, key, value) VALUES ('config1', 'key1', 'value1');
* INSERT INTO myconfigs (name, key, value) VALUES ('config2', 'key2', 'value2');
*
*
* Configuration config1 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config1");
* String value1 = conf.getString("key1");
*
* Configuration config2 = new DatabaseConfiguration(datasource, "myconfigs", "name", "key", "value", "config2");
* String value2 = conf.getString("key2");
* </pre>
* <h1>Note: Like JDBC itself, protection against SQL injection is left to the user.</h1>
* @since 1.0
*
* @author <a href="mailto:ebourg@apache.org">Emmanuel Bourg</a>
* @version $Revision: 613503 $, $Date: 2008-01-20 05:39:08 +0100 (So, 20 Jan 2008) $
*/
public class DatabaseConfiguration extends AbstractConfiguration
{
/** The datasource to connect to the database. */
private DataSource datasource;
/** The name of the table containing the configurations. */
private String table;
/** The column containing the name of the configuration. */
private String nameColumn;
/** The column containing the keys. */
private String keyColumn;
/** The column containing the values. */
private String valueColumn;
/** The name of the configuration. */
private String name;
/**
* Build a configuration from a table containing multiple configurations.
*
* @param datasource the datasource to connect to the database
* @param table the name of the table containing the configurations
* @param nameColumn the column containing the name of the configuration
* @param keyColumn the column containing the keys of the configuration
* @param valueColumn the column containing the values of the configuration
* @param name the name of the configuration
*/
public DatabaseConfiguration(DataSource datasource, String table, String nameColumn,
String keyColumn, String valueColumn, String name)
{
this.datasource = datasource;
this.table = table;
this.nameColumn = nameColumn;
this.keyColumn = keyColumn;
this.valueColumn = valueColumn;
this.name = name;
setLogger(LogFactory.getLog(getClass()));
addErrorLogListener(); // log errors per default
}
/**
* Build a configuration from a table.-
*
* @param datasource the datasource to connect to the database
* @param table the name of the table containing the configurations
* @param keyColumn the column containing the keys of the configuration
* @param valueColumn the column containing the values of the configuration
*/
public DatabaseConfiguration(DataSource datasource, String table, String keyColumn, String valueColumn)
{
this(datasource, table, null, keyColumn, valueColumn, null);
}
/**
* Returns the value of the specified property. If this causes a database
* error, an error event will be generated of type
* <code>EVENT_READ_PROPERTY</code> with the causing exception. The
* event's <code>propertyName</code> is set to the passed in property key,
* the <code>propertyValue</code> is undefined.
*
* @param key the key of the desired property
* @return the value of this property
*/
public Object getProperty(String key)
{
Object result = null;
// build the query
StringBuffer query = new StringBuffer("SELECT * FROM ");
query.append(table).append(" WHERE ");
query.append(keyColumn).append("=?");
if (nameColumn != null)
{
query.append(" AND " + nameColumn + "=?");
}
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
// bind the parameters
pstmt = conn.prepareStatement(query.toString());
pstmt.setString(1, key);
if (nameColumn != null)
{
pstmt.setString(2, name);
}
ResultSet rs = pstmt.executeQuery();
List results = new ArrayList();
while (rs.next())
{
Object value = rs.getObject(valueColumn);
if (isDelimiterParsingDisabled())
{
results.add(value);
}
else
{
// Split value if it containts the list delimiter
CollectionUtils.addAll(results, PropertyConverter.toIterator(value, getListDelimiter()));
}
}
if (!results.isEmpty())
{
result = (results.size() > 1) ? results : results.get(0);
}
}
catch (SQLException e)
{
fireError(EVENT_READ_PROPERTY, key, null, e);
}
finally
{
close(conn, pstmt);
}
return result;
}
/**
* Adds a property to this configuration. If this causes a database error,
* an error event will be generated of type <code>EVENT_ADD_PROPERTY</code>
* with the causing exception. The event's <code>propertyName</code> is
* set to the passed in property key, the <code>propertyValue</code>
* points to the passed in value.
*
* @param key the property key
* @param obj the value of the property to add
*/
protected void addPropertyDirect(String key, Object obj)
{
// build the query
StringBuffer query = new StringBuffer("INSERT INTO " + table);
if (nameColumn != null)
{
query.append(" (" + nameColumn + ", " + keyColumn + ", " + valueColumn + ") VALUES (?, ?, ?)");
}
else
{
query.append(" (" + keyColumn + ", " + valueColumn + ") VALUES (?, ?)");
}
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
// bind the parameters
pstmt = conn.prepareStatement(query.toString());
int index = 1;
if (nameColumn != null)
{
pstmt.setString(index++, name);
}
pstmt.setString(index++, key);
pstmt.setString(index++, String.valueOf(obj));
pstmt.executeUpdate();
}
catch (SQLException e)
{
fireError(EVENT_ADD_PROPERTY, key, obj, e);
}
finally
{
// clean up
close(conn, pstmt);
}
}
/**
* Adds a property to this configuration. This implementation will
* temporarily disable list delimiter parsing, so that even if the value
* contains the list delimiter, only a single record will be written into
* the managed table. The implementation of <code>getProperty()</code>
* will take care about delimiters. So list delimiters are fully supported
* by <code>DatabaseConfiguration</code>, but internally treated a bit
* differently.
*
* @param key the key of the new property
* @param value the value to be added
*/
public void addProperty(String key, Object value)
{
boolean parsingFlag = isDelimiterParsingDisabled();
try
{
if (value instanceof String)
{
// temporarily disable delimiter parsing
setDelimiterParsingDisabled(true);
}
super.addProperty(key, value);
}
finally
{
setDelimiterParsingDisabled(parsingFlag);
}
}
/**
* Checks if this configuration is empty. If this causes a database error,
* an error event will be generated of type <code>EVENT_READ_PROPERTY</code>
* with the causing exception. Both the event's <code>propertyName</code>
* and <code>propertyValue</code> will be undefined.
*
* @return a flag whether this configuration is empty.
*/
public boolean isEmpty()
{
boolean empty = true;
// build the query
StringBuffer query = new StringBuffer("SELECT count(*) FROM " + table);
if (nameColumn != null)
{
query.append(" WHERE " + nameColumn + "=?");
}
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
// bind the parameters
pstmt = conn.prepareStatement(query.toString());
if (nameColumn != null)
{
pstmt.setString(1, name);
}
ResultSet rs = pstmt.executeQuery();
if (rs.next())
{
empty = rs.getInt(1) == 0;
}
}
catch (SQLException e)
{
fireError(EVENT_READ_PROPERTY, null, null, e);
}
finally
{
// clean up
close(conn, pstmt);
}
return empty;
}
/**
* Checks whether this configuration contains the specified key. If this
* causes a database error, an error event will be generated of type
* <code>EVENT_READ_PROPERTY</code> with the causing exception. The
* event's <code>propertyName</code> will be set to the passed in key, the
* <code>propertyValue</code> will be undefined.
*
* @param key the key to be checked
* @return a flag whether this key is defined
*/
public boolean containsKey(String key)
{
boolean found = false;
// build the query
StringBuffer query = new StringBuffer("SELECT * FROM " + table + " WHERE " + keyColumn + "=?");
if (nameColumn != null)
{
query.append(" AND " + nameColumn + "=?");
}
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
// bind the parameters
pstmt = conn.prepareStatement(query.toString());
pstmt.setString(1, key);
if (nameColumn != null)
{
pstmt.setString(2, name);
}
ResultSet rs = pstmt.executeQuery();
found = rs.next();
}
catch (SQLException e)
{
fireError(EVENT_READ_PROPERTY, key, null, e);
}
finally
{
// clean up
close(conn, pstmt);
}
return found;
}
/**
* Removes the specified value from this configuration. If this causes a
* database error, an error event will be generated of type
* <code>EVENT_CLEAR_PROPERTY</code> with the causing exception. The
* event's <code>propertyName</code> will be set to the passed in key, the
* <code>propertyValue</code> will be undefined.
*
* @param key the key of the property to be removed
*/
public void clearProperty(String key)
{
// build the query
StringBuffer query = new StringBuffer("DELETE FROM " + table + " WHERE " + keyColumn + "=?");
if (nameColumn != null)
{
query.append(" AND " + nameColumn + "=?");
}
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
// bind the parameters
pstmt = conn.prepareStatement(query.toString());
pstmt.setString(1, key);
if (nameColumn != null)
{
pstmt.setString(2, name);
}
pstmt.executeUpdate();
}
catch (SQLException e)
{
fireError(EVENT_CLEAR_PROPERTY, key, null, e);
}
finally
{
// clean up
close(conn, pstmt);
}
}
/**
* Removes all entries from this configuration. If this causes a database
* error, an error event will be generated of type
* <code>EVENT_CLEAR</code> with the causing exception. Both the
* event's <code>propertyName</code> and the <code>propertyValue</code>
* will be undefined.
*/
public void clear()
{
// build the query
StringBuffer query = new StringBuffer("DELETE FROM " + table);
if (nameColumn != null)
{
query.append(" WHERE " + nameColumn + "=?");
}
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
// bind the parameters
pstmt = conn.prepareStatement(query.toString());
if (nameColumn != null)
{
pstmt.setString(1, name);
}
pstmt.executeUpdate();
}
catch (SQLException e)
{
fireError(EVENT_CLEAR, null, null, e);
}
finally
{
// clean up
close(conn, pstmt);
}
}
/**
* Returns an iterator with the names of all properties contained in this
* configuration. If this causes a database
* error, an error event will be generated of type
* <code>EVENT_READ_PROPERTY</code> with the causing exception. Both the
* event's <code>propertyName</code> and the <code>propertyValue</code>
* will be undefined.
* @return an iterator with the contained keys (an empty iterator in case
* of an error)
*/
public Iterator getKeys()
{
Collection keys = new ArrayList();
// build the query
StringBuffer query = new StringBuffer("SELECT DISTINCT " + keyColumn + " FROM " + table);
if (nameColumn != null)
{
query.append(" WHERE " + nameColumn + "=?");
}
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = getConnection();
// bind the parameters
pstmt = conn.prepareStatement(query.toString());
if (nameColumn != null)
{
pstmt.setString(1, name);
}
ResultSet rs = pstmt.executeQuery();
while (rs.next())
{
keys.add(rs.getString(1));
}
}
catch (SQLException e)
{
fireError(EVENT_READ_PROPERTY, null, null, e);
}
finally
{
// clean up
close(conn, pstmt);
}
return keys.iterator();
}
/**
* Returns the used <code>DataSource</code> object.
*
* @return the data source
* @since 1.4
*/
public DataSource getDatasource()
{
return datasource;
}
/**
* Returns a <code>Connection</code> object. This method is called when
* ever the database is to be accessed. This implementation returns a
* connection from the current <code>DataSource</code>.
*
* @return the <code>Connection</code> object to be used
* @throws SQLException if an error occurs
* @since 1.4
* @deprecated Use a custom data source to change the connection used by the
* class. To be removed in Commons Configuration 2.0
*/
protected Connection getConnection() throws SQLException
{
return getDatasource().getConnection();
}
/**
* Close a <code>Connection</code> and, <code>Statement</code>.
* Avoid closing if null and hide any SQLExceptions that occur.
*
* @param conn The database connection to close
* @param stmt The statement to close
*/
private void close(Connection conn, Statement stmt)
{
try
{
if (stmt != null)
{
stmt.close();
}
}
catch (SQLException e)
{
getLogger().error("An error occured on closing the statement", e);
}
try
{
if (conn != null)
{
conn.close();
}
}
catch (SQLException e)
{
getLogger().error("An error occured on closing the connection", e);
}
}
}